--EXEC sp_sort_bin_closed_lot_report '0', '0', '0', '0', '0', '0', '0'
ALTER proc sp_sort_bin_closed_lot_report

@STEP_ID VARCHAR (50),
@PRODUCT_ID VARCHAR (50),
@DATE_FROM VARCHAR (50),
@DATE_TO VARCHAR (50),
@LOCATION_FROM VARCHAR(50),
@LOT_STATUS VARCHAR(50),
@DEFAULT_PROMPT VARCHAR(50)

AS
BEGIN

	DECLARE @SORT_REL_ID 	INT
	DECLARE @SORT_REL_NUMB 	VARCHAR(50)
	DECLARE @SORT_REL_DATE 	VARCHAR(50)
	DECLARE @SORT_REL_PO	VARCHAR(50)
	DECLARE @PROCESSSTEP_ID	BIGINT
	DECLARE @VENDOR_NAME	VARCHAR(50)
	DECLARE @STATUS		VARCHAR(50)
	DECLARE @LOT_ID		INT
	DECLARE @LOT_NUMBER	VARCHAR(50)
	DECLARE @QTY_AVAIL	INT
	DECLARE @QTY_PULLED	INT
	DECLARE @SORT_WAFER_ID	VARCHAR(300)
	DECLARE @PART_NUMBER	VARCHAR(50)
	DECLARE @BASE_PRODUCT 	VARCHAR(50)
	DECLARE @PS_TYPE 	VARCHAR(50)
	DECLARE @GOOD_DICE 	INT
	DECLARE @BIN_1 		INT
	DECLARE @BIN_2 		INT
	DECLARE @BIN_3		INT
	DECLARE @BIN_4		INT
	DECLARE @BIN_5 		INT
	DECLARE @WAFER_ID 	INT
	DECLARE @YIELD 		FLOAT
	DECLARE @BIN_REJECTED   INT
	DECLARE @DATE_OUT	DATETIME

	DECLARE @SORT_LOT_WAFER_ID VARCHAR(300)

	DECLARE @WAFER_IDS_TABLE TABLE ( LOT_WAFER_ID INT )
	DECLARE @RESULTS TABLE ( PART_NUMBER		VARCHAR(50),
				 BASE_PRODUCT 		VARCHAR(50),
				 PRODUCT_ID		INT,
				 LOT_ID			INT,
				 LOT_NUMBER		VARCHAR(50),
				 SORT_ALL_WAFER_ID	VARCHAR(100),
				 PS_TYPE 		VARCHAR(50),
				 QTY_AVAIL		INT,
				 QTY_PULLED		INT,
				 STATUS			VARCHAR(50),
				 SORT_REL_ID 		INT,
				 SORT_REL_NUMB 		VARCHAR(50),
				 SORT_REL_DATE 		VARCHAR(50),
				 SORT_REL_PO		VARCHAR(50),
				 PROCESSSTEP_ID		BIGINT,
				 VENDOR_NAME		VARCHAR(50),
				 WAFER_ID		INT,
				 GOOD_DICE 		INT,
				 BIN_1 			INT,
				 BIN_2 			INT,
				 BIN_3			INT,
				 BIN_4			INT,
				 BIN_5 			INT,
				 DATE_OUT		DATETIME )
				 

	DECLARE @RESULTS_TABLE TABLE ( 	PART_NUMBER		VARCHAR(50),
				 	BASE_PRODUCT 		VARCHAR(50),
					PRODUCT_ID		INT,
				 	LOT_ID			INT,
				 	LOT_NUMBER		VARCHAR(50),
				 	SORT_ALL_WAFER_ID 	VARCHAR(100),
				 	PS_TYPE 		VARCHAR(50),
				 	QTY_AVAIL		INT,
				 	QTY_PULLED		INT,
				 	STATUS			VARCHAR(50),
				 	SORT_REL_ID 		INT,
				 	SORT_REL_NUMB 		VARCHAR(50),
				 	SORT_REL_DATE 		VARCHAR(50),
				 	SORT_REL_PO		VARCHAR(50),
				 	PROCESSSTEP_ID		BIGINT,
				 	VENDOR_NAME		VARCHAR(50),
				 	WAFER_ID		INT,
				 	GOOD_DICE 		INT,
				 	BIN_1 			INT,
				 	BIN_2 			INT,
				 	BIN_3			INT,
				 	BIN_4			INT,
				 	BIN_5 			INT,
					YIELD 			FLOAT,
					BIN_REJECTED		INT,
					DATE_OUT		DATETIME) 

	DECLARE @RELEASECURSOR CURSOR
	DECLARE @LOTCURSOR CURSOR

	DECLARE @CURRENT_QUARTER INT
	SET @CURRENT_QUARTER = DATEPART(q, GETDATE()) 
	DECLARE @CURRENT_YEAR INT
	SET @CURRENT_YEAR = DATEPART(yyyy, GETDATE()) 

	IF @CURRENT_QUARTER = 1
	BEGIN
		IF LTRIM(RTRIM(@DATE_FROM)) IS NULL OR LTRIM(RTRIM(@DATE_FROM)) = '0'
		BEGIN
			SET @DATE_FROM = '1/1/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
		IF LTRIM(RTRIM(@DATE_TO)) IS NULL OR LTRIM(RTRIM(@DATE_TO)) = '0'
		BEGIN
			SET @DATE_TO = '3/31/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
	END
	ELSE IF @CURRENT_QUARTER = 2
	BEGIN
		IF LTRIM(RTRIM(@DATE_FROM)) IS NULL OR LTRIM(RTRIM(@DATE_FROM)) = '0'
		BEGIN
			SET @DATE_FROM = '4/1/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
		IF LTRIM(RTRIM(@DATE_TO)) IS NULL OR LTRIM(RTRIM(@DATE_TO)) = '0'
		BEGIN
			SET @DATE_TO = '6/30/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
	END
	ELSE IF @CURRENT_QUARTER = 3
	BEGIN
		IF LTRIM(RTRIM(@DATE_FROM)) IS NULL OR LTRIM(RTRIM(@DATE_FROM)) = '0'
		BEGIN
			SET @DATE_FROM = '7/1/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
		IF LTRIM(RTRIM(@DATE_TO)) IS NULL OR LTRIM(RTRIM(@DATE_TO)) = '0'
		BEGIN
			SET @DATE_TO = '9/30/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
	END
	ELSE IF @CURRENT_QUARTER = 4
	BEGIN
		IF LTRIM(RTRIM(@DATE_FROM)) IS NULL OR LTRIM(RTRIM(@DATE_FROM)) = '0'
		BEGIN
			SET @DATE_FROM = '10/1/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
		IF LTRIM(RTRIM(@DATE_TO)) IS NULL OR LTRIM(RTRIM(@DATE_TO)) = '0'
		BEGIN
			SET @DATE_TO = '12/31/'+CONVERT(VARCHAR, @CURRENT_YEAR)
		END
	END


	/*INSERT INTO @RESULTS SELECT P.PART_NUMBER,( SELECT P.PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			 	CASE 
					WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
			 	END ) AS BASE_PRODUCT,P.P_PARENT_PRODUCT_ID,
				SRLT.LOT_ID,SRLT.LOT_NUMBER,SRLT.WAFER_ID,'SORT',SRLT.QTY_AVAIL,SRLT.QTY_PULLED,
				SRT.STATUS, SRT.SORT_REL_ID,  SRT.SORT_REL_NUMB, SRT.SORT_REL_DATE, SRT.SORT_REL_PO, SRT.PROCESSSTEP_ID,
				(SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = SRT.VEND_ID) AS VENDOR_NAME, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL
				FROM SORT_RELEASE_TABLE SRT,SORT_RELEASE_LOT_TABLE SRLT,PRODUCT P
				WHERE SRT.SORT_REL_ID = SRLT.SORT_REL_ID 
				AND SRT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND SRT.STATUS = 'CLOSED' */

	INSERT INTO @RESULTS SELECT P.PART_NUMBER,( SELECT P.PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			 	CASE 
					WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
			 	END ) AS BASE_PRODUCT,P.P_PARENT_PRODUCT_ID,
				SRLT.LOT_ID,SRLT.LOT_NUMBER,SRLT.WAFER_ID,'SORT',SRLT.QTY_AVAIL,SRLT.QTY_PULLED,
				SRT.STATUS, SRT.SORT_REL_ID,  SRT.SORT_REL_NUMB, SRT.SORT_REL_DATE, SRT.SORT_REL_PO, SRT.PROCESSSTEP_ID,
				(SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = SRT.VEND_ID) AS VENDOR_NAME, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
				(SELECT TOP 1 CONVERT(DATETIME , ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) ) FROM SORT_LOT_HISTORY SLH,LOT_TABLE LT 
					WHERE SLH.LOT_ID = LT.LOT_ID 
					AND LT.LOT_NUMBER = SRLT.LOT_NUMBER
					AND LT.PROCESSSTEP_ID = SRT.PROCESSSTEP_ID ORDER BY SLH.TIMESTAMP DESC ) AS DATE_OUT 				
				FROM SORT_RELEASE_TABLE SRT,SORT_RELEASE_LOT_TABLE SRLT,PRODUCT P
				WHERE SRT.SORT_REL_ID = SRLT.SORT_REL_ID 
				AND SRT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND SRT.STATUS = 'CLOSED' 
				AND ( (SELECT TOP 1 CONVERT(DATETIME , ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) ) FROM SORT_LOT_HISTORY SLH,LOT_TABLE LT 
										WHERE SLH.LOT_ID = LT.LOT_ID 
										AND LT.LOT_NUMBER = SRLT.LOT_NUMBER
										AND LT.PROCESSSTEP_ID = SRT.PROCESSSTEP_ID ORDER BY SLH.TIMESTAMP DESC ) >= CONVERT(DATETIME, @DATE_FROM) 
				AND (SELECT TOP 1 CONVERT(DATETIME , ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) ) FROM SORT_LOT_HISTORY SLH,LOT_TABLE LT 
										WHERE SLH.LOT_ID = LT.LOT_ID 
										AND LT.LOT_NUMBER = SRLT.LOT_NUMBER
										AND LT.PROCESSSTEP_ID = SRT.PROCESSSTEP_ID ORDER BY SLH.TIMESTAMP DESC ) <= CONVERT(DATETIME, @DATE_TO)  )
	
	--IF( @PRODUCT_ID <> '0' )
	--BEGIN
		--DELETE FROM @RESULTS WHERE PRODUCT_ID <> @PRODUCT_ID
	--END

	IF( @STEP_ID <> '0' )
	BEGIN
		DELETE FROM @RESULTS WHERE PROCESSSTEP_ID <> @STEP_ID
	END
	ELSE
	BEGIN
		IF( @PRODUCT_ID <> '0' )
		BEGIN
			DELETE FROM @RESULTS WHERE PRODUCT_ID <> @PRODUCT_ID
		END
	END

	
	
	SET @RELEASECURSOR = CURSOR FOR SELECT SORT_REL_ID, LOT_NUMBER, SORT_ALL_WAFER_ID,PROCESSSTEP_ID FROM @RESULTS
	OPEN @RELEASECURSOR
	FETCH NEXT FROM @RELEASECURSOR INTO @SORT_REL_ID, @LOT_NUMBER, @SORT_WAFER_ID,@PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		DELETE FROM @WAFER_IDS_TABLE
		SET @SORT_LOT_WAFER_ID = REPLACE( @SORT_WAFER_ID , ';', ',' )
		INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SORT_LOT_WAFER_ID )

		SET @LOTCURSOR = CURSOR FOR SELECT LOT_WAFER_ID FROM @WAFER_IDS_TABLE
		OPEN @LOTCURSOR
		FETCH NEXT FROM @LOTCURSOR INTO @WAFER_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			--SELECT TOP 1 @DATE_OUT = CONVERT(DATETIME , ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) ) FROM SORT_LOT_HISTORY SLH, LOT_TABLE LT 
			--WHERE SLH.LOT_ID = LT.LOT_ID AND LT.LOT_NUMBER = @LOT_NUMBER AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID ORDER BY SLH.TIMESTAMP DESC

			SELECT @GOOD_DICE=GOOD_DICE, @BIN_1=ISNULL ( BIN_1, 0 ), @BIN_2=ISNULL ( BIN_2, 0 ), @BIN_3=ISNULL ( BIN_3, 0 ), @BIN_4=ISNULL ( BIN_4, 0 ), @BIN_5=ISNULL ( BIN_5, 0 ),
			@YIELD = YIELD , @BIN_REJECTED = BIN_REJECTED  
			FROM INFORMATIVE_BINNING IB,LOT_TABLE LT WHERE IB.LOT_ID = LT.LOT_ID AND LT.LOT_NUMBER = @LOT_NUMBER AND WAFER_ID = @WAFER_ID AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID
			
			IF ( @GOOD_DICE > 0 )	
			BEGIN 
				INSERT INTO @RESULTS_TABLE
				SELECT PART_NUMBER, BASE_PRODUCT, PRODUCT_ID,LOT_ID, LOT_NUMBER, NULL, PS_TYPE, QTY_AVAIL, QTY_PULLED, 
				STATUS, SORT_REL_ID, SORT_REL_NUMB, SORT_REL_DATE, SORT_REL_PO, PROCESSSTEP_ID, VENDOR_NAME, @WAFER_ID, @GOOD_DICE, @BIN_1, @BIN_2, @BIN_3, @BIN_4, @BIN_5,@YIELD , @BIN_REJECTED,DATE_OUT
				FROM @RESULTS WHERE SORT_REL_ID = @SORT_REL_ID
				
			END
			
			SET @WAFER_ID		= NULL
			SET @GOOD_DICE		= NULL
			SET @BIN_1  		= NULL
			SET @BIN_2 		= NULL
			SET @BIN_3 		= NULL
			SET @BIN_4 		= NULL
			SET @BIN_5 		= NULL
			SET @YIELD 		= NULL
			SET @BIN_REJECTED 	= NULL
			SET @DATE_OUT 		= NULL

			FETCH NEXT FROM @LOTCURSOR INTO @WAFER_ID
		END
		CLOSE @LOTCURSOR
		DEALLOCATE @LOTCURSOR
		
		SET @SORT_REL_ID = NULL
		SET @LOT_ID = NULL
		SET @WAFER_ID = NULL
		SET @SORT_LOT_WAFER_ID = NULL
		FETCH NEXT FROM @RELEASECURSOR INTO @SORT_REL_ID, @LOT_NUMBER, @SORT_WAFER_ID,@PROCESSSTEP_ID
	END
	CLOSE @RELEASECURSOR
	DEALLOCATE @RELEASECURSOR 
	
	SELECT * FROM @RESULTS_TABLE
END